package com.shop.dao.impl;

import com.shop.dao.GoodsDao;
import com.shop.entity.*;
import com.shop.exception.DAOException;
import com.shop.transaction.TransactionManager;
import com.shop.util.ConnectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

 public class GoodsDaoImpl implements GoodsDao {

    @Override
    public List<GoodsFirstType> queryAllGft() throws DAOException {
        List<GoodsFirstType> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("SELECT * FROM GOODS_FIRST_TYPE");
            rs = pstmt.executeQuery();
            while (rs.next()){
                GoodsFirstType gft = new GoodsFirstType();
                gft.setGft_id(rs.getInt(1));
                gft.setGft_name(rs.getString(2));
                list.add(gft);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new DAOException(throwables);
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }

    @Override
    public List<GoodsSecondType> queryAllGst(int gft_id) throws DAOException {
        List<GoodsSecondType> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("SELECT * FROM GOODS_SECOND_TYPE WHERE GFT_ID = ?");
            pstmt.setInt(1,gft_id);
            rs = pstmt.executeQuery();
            while (rs.next()){
                GoodsSecondType gst = new GoodsSecondType();
                gst.setGst_id(rs.getInt(1));
                gst.setGft_id(rs.getInt(2));
                gst.setGst_name(rs.getString(3));
                list.add(gst);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new DAOException(throwables);
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }

    @Override
    public List<GoodsThirdType> queryAllGtt(int gst_id) throws DAOException {
        List<GoodsThirdType> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("SELECT * FROM GOODS_THIRD_TYPE WHERE GST_ID = ?");
            pstmt.setInt(1,gst_id);
            rs = pstmt.executeQuery();
            while (rs.next()){
                GoodsThirdType gtt = new GoodsThirdType();
                gtt.setGtt_id(rs.getInt(1));
                gtt.setGst_id(rs.getInt(2));
                gtt.setGtt_name(rs.getString(3));
                list.add(gtt);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new DAOException(throwables);
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return list;
    }

     @Override
     public GoodsFirstType queryFirstByName(String gft_name) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOODS_FIRST_TYPE WHERE GFT_NAME=?");
             pstmt.setString(1,gft_name);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 GoodsFirstType goodsFirstType = new GoodsFirstType();
                 int gft_id =rs.getInt("GFT_ID");
                 goodsFirstType.setGft_id(gft_id);
                 return goodsFirstType;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

     @Override
     public GoodsSecondType querySecondByName(String gst_name) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOODS_SECOND_TYPE WHERE GST_NAME=?");
             pstmt.setString(1,gst_name);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 GoodsSecondType goodsSecondType = new GoodsSecondType();
                 int gst_id =rs.getInt("GST_ID");
                 goodsSecondType.setGst_id(gst_id);
                 int gft_id = rs.getInt("GFT_ID");
                 goodsSecondType.setGft_id(gft_id);
                 return goodsSecondType;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

     @Override
     public GoodsThirdType queryThirdByName(String gtt_name) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOODS_THIRD_TYPE WHERE GTT_NAME=?");
             pstmt.setString(1,gtt_name);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 GoodsThirdType goodsThirdType = new GoodsThirdType();
                 int gtt_id =rs.getInt("GTT_ID");
                 goodsThirdType.setGtt_id(gtt_id);
                 int gst_id = rs.getInt("GST_ID");
                 goodsThirdType.setGst_id(gst_id);
                 return goodsThirdType;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

     @Override
     public GoodsFourType queryGfourByName(String gfour_name) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOODS_FOUR_TYPE WHERE GFOUR_NAME=?");
             pstmt.setString(1,gfour_name);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 GoodsFourType goodsFourType = new GoodsFourType();
                 int gfour_id =rs.getInt("GFOUR_ID");
                 goodsFourType.setGfour_id(gfour_id);
                 int gtt_id = rs.getInt("GTT_ID");
                 goodsFourType.setGtt_id(gtt_id);
                 goodsFourType.setGfour_name(gfour_name);
                 return goodsFourType;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

     @Override
    public void addGoods(Goods goods) throws DAOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("INSERT INTO GOODS(G_NAME,G_DATA,G_DATAIL) VALUES(?,?,?)");
            pstmt.setString(1,goods.getG_name());
            pstmt.setString(2,goods.getG_data());
            pstmt.setString(3,goods.getG_datail());
            pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new DAOException(throwables);
        }finally {
            ConnectionUtils.close(pstmt,null);
        }
    }

    @Override
    public void addGoodDetail(GoodDetail goodDetail) throws DAOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("INSERT INTO GOOD_DETAIL(GFT_ID,GST_ID,GTT_ID,GFOUR_ID,GD_PRICE,GD_NUM,G_ID,S_ID,UU_ID) VALUES(?,?,?,?,?,?,?,?,?)");
            pstmt.setInt(1,goodDetail.getGft_id());
            pstmt.setInt(2,goodDetail.getGst_id());
            pstmt.setInt(3,goodDetail.getGtt_id());
            pstmt.setInt(4,goodDetail.getGfour_id());
            pstmt.setDouble(5,goodDetail.getGd_price());
            pstmt.setInt(6,goodDetail.getGd_num());
            pstmt.setInt(7,goodDetail.getG_id());
            pstmt.setInt(8,goodDetail.getS_id());
            pstmt.setString(9,goodDetail.getUu_id());
            pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new DAOException(throwables);
        }finally {
            ConnectionUtils.close(pstmt,null);
        }
    }

    @Override
    public void addImgPath(ImgPath imgPath) throws DAOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("INSERT INTO IMG_PATH(IMG_PATH,GD_ID) VALUES(?,?)");
            pstmt.setString(1,imgPath.getImg_path());
            pstmt.setInt(2,imgPath.getGd_id());
            pstmt.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new DAOException(throwables);
        }finally {
            ConnectionUtils.close(pstmt,null);
        }
    }


    @Override
    public ImgPath queryImgPath(int gd_id) throws DAOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("SELECT * FROM IMG_PATH WHERE GD_ID=?");
            pstmt.setInt(1,gd_id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                ImgPath imgPath = new ImgPath();
                int img_id =rs.getInt("IMG_ID");
                imgPath.setImg_id(img_id);
                String img_path = rs.getString("IMG_PATH");
                imgPath.setImg_path(img_path);
                return imgPath;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new DAOException(throwables);
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return null;
    }
     @Override
     public ImgPath queryImgPathById(int img_id) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM IMG_PATH WHERE IMG_ID=?");
             pstmt.setInt(1,img_id);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 ImgPath imgPath = new ImgPath();
                 imgPath.setImg_id(img_id);
                 int gd_id =rs.getInt("GD_ID");
                 imgPath.setGd_id(gd_id);
                 String img_path = rs.getString("IMG_PATH");
                 imgPath.setImg_path(img_path);
                 return imgPath;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

    @Override
    public GoodDetail queryGoodDetailById(int gd_id) throws DAOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("SELECT * FROM GOOD_DETAIL WHERE GD_ID=?");
            pstmt.setInt(1,gd_id);
            rs = pstmt.executeQuery();
            if(rs.next()){
                GoodDetail goodDetail = new GoodDetail();
                int gdId = rs.getInt("gd_id");
                goodDetail.setGd_id(gdId);
                int gft_id = rs.getInt("GFT_ID");
                goodDetail.setGft_id(gft_id);
                int gst_id = rs.getInt("GST_ID");
                goodDetail.setGst_id(gst_id);
                int gtt_id = rs.getInt("GTT_ID");
                goodDetail.setGtt_id(gtt_id);
                int gfour_id = rs.getInt("GFOUR_ID");
                goodDetail.setGfour_id(gfour_id);
                Double gd_price = rs.getDouble("GD_PRICE");
                goodDetail.setGd_price(gd_price);
                int gd_num = rs.getInt("GD_NUM");
                goodDetail.setGd_num(gd_num);
                int g_id = rs.getInt("G_ID");
                goodDetail.setG_id(g_id);
                int s_id = rs.getInt("S_ID");
                goodDetail.setS_id(s_id);
                int gd_buyNum = rs.getInt("GD_BUYNUM");
                goodDetail.setGd_buyNum(gd_buyNum);
                return goodDetail;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new DAOException(throwables);
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return null;
    }

     @Override
     public void addGoodsFourType(GoodsFourType goodsFourType) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("INSERT INTO GOODS_FOUR_TYPE(GTT_ID,GFOUR_NAME) VALUES(?,?)");
             pstmt.setInt(1,goodsFourType.getGtt_id());
             pstmt.setString(2,goodsFourType.getGfour_name());
             pstmt.executeUpdate();
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(pstmt,null);
         }
     }

     @Override
     public GoodsFourType queryGfourByIdName(int gtt_id,String gfour_name) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOODS_FOUR_TYPE WHERE GTT_ID=? AND GFOUR_NAME=?");
             pstmt.setInt(1,gtt_id);
             pstmt.setString(2,gfour_name);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 GoodsFourType goodsFourType = new GoodsFourType();
                 int gfour_id = rs.getInt("GFOUR_ID");
                 goodsFourType.setGfour_id(gfour_id);
                 goodsFourType.setGtt_id(gtt_id);
                 goodsFourType.setGfour_name(gfour_name);
                 return goodsFourType;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

     @Override
     public int updateGoodsFourType(GoodsFourType goodsFourType) throws DAOException {
         Connection conn = null;
         PreparedStatement ps = null;
         ResultSet rs = null;
         int result = 0;
         try {
             conn = TransactionManager.getTransactionConn();
             String sql = "update goods_four_type set gtt_id=?,gfour_name=? where gfour_id=?";
             ps = conn.prepareStatement(sql);
             ps.setInt(1,goodsFourType.getGtt_id());
             ps.setString(2,goodsFourType.getGfour_name());
             ps.setInt(3,goodsFourType.getGfour_id());
             result = ps.executeUpdate();
         } catch (SQLException e) {
             System.out.println(e.getMessage());
             e.printStackTrace();
             throw new DAOException(e);
         }finally {
             ConnectionUtils.close(rs,ps,null);
         }
         return result;
     }

     @Override
     public int updateGoods(Goods goods) throws DAOException {
         Connection conn = null;
         PreparedStatement ps = null;
         ResultSet rs = null;
         int result = 0;
         try {
             conn = TransactionManager.getTransactionConn();
             String sql = "update goods set g_name=?,g_data=?,g_datail=? where g_id=?";
             ps = conn.prepareStatement(sql);
             ps.setString(1,goods.getG_name());
             ps.setString(2,goods.getG_data());
             ps.setString(3,goods.getG_datail());
             ps.setInt(4,goods.getG_id());
             result = ps.executeUpdate();
         } catch (SQLException e) {
             System.out.println(e.getMessage());
             e.printStackTrace();
             throw new DAOException(e);
         }finally {
             ConnectionUtils.close(rs,ps,null);
         }
         return result;
     }

     @Override
     public int updateGoodDetail(GoodDetail goodDetail) throws DAOException {
         Connection conn = null;
         PreparedStatement ps = null;
         ResultSet rs = null;
         int result = 0;

         try {
             conn = TransactionManager.getTransactionConn();
             String sql = "update good_detail set gft_id=?,gst_id=?,gtt_id=?,gfour_id=?,gd_price=?,gd_num=? where gd_id=?";
             ps = conn.prepareStatement(sql);
             ps.setInt(1,goodDetail.getGft_id());
             ps.setInt(2,goodDetail.getGst_id());
             ps.setInt(3,goodDetail.getGtt_id());
             ps.setInt(4,goodDetail.getGfour_id());
             ps.setDouble(5,goodDetail.getGd_price());
             ps.setInt(6,goodDetail.getGd_num());
             ps.setInt(7,goodDetail.getGd_id());
             result = ps.executeUpdate();
         } catch (SQLException e) {
             System.out.println(e.getMessage());
             e.printStackTrace();
             throw new DAOException(e);
         }finally {
             ConnectionUtils.close(rs,ps,null);
         }
         return result;
     }

     @Override
     public int updateImgPath(ImgPath imgPath) throws DAOException {
         Connection conn = null;
         PreparedStatement ps = null;
         ResultSet rs = null;
         int result = 0;
         try {
             conn = TransactionManager.getTransactionConn();
             String sql = "update img_path set img_path=? where img_id=?";
             ps = conn.prepareStatement(sql);
             ps.setString(1,imgPath.getImg_path());
             ps.setInt(2,imgPath.getImg_id());
             result = ps.executeUpdate();
         } catch (SQLException e) {
             System.out.println(e.getMessage());
             e.printStackTrace();
             throw new DAOException(e);
         }finally {
             ConnectionUtils.close(rs,ps,null);
         }
         return result;
     }

     @Override
     public GoodsFourType queryGfourById(int gfour_id) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOODS_FOUR_TYPE WHERE GFOUR_ID=?");
             pstmt.setInt(1,gfour_id);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 GoodsFourType goodsFourType = new GoodsFourType();
                 goodsFourType.setGfour_id(gfour_id);
                 int gtt_id = rs.getInt("GTT_ID");
                 goodsFourType.setGtt_id(gtt_id);
                 String gfour_name =rs.getString("GFOUR_NAME");
                 goodsFourType.setGfour_name(gfour_name);
                 return goodsFourType;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

     @Override
     public List<GoodsListVO> queryAllGoodDetail(int s_id) throws DAOException {
         List<GoodsListVO> list = new ArrayList<>();
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("select a.gd_id,b.g_id,c.img_path,b.g_name,b.g_data,b.g_datail,a.gd_price,a.gd_num,d.gfour_name\n" +
                     "from good_detail a\n" +
                     "join goods b on a.g_id = b.g_id\n" +
                     "join img_path c on a.gd_id = c.gd_id\n" +
                     "join goods_four_type d on a.gfour_id = d.gfour_id\n" +
                     "where a.s_id=?;");
             pstmt.setInt(1,s_id);
             rs = pstmt.executeQuery();
             while (rs.next()){
                 GoodsListVO goodsListVO = new GoodsListVO();
                 goodsListVO.setGd_id(rs.getInt("GD_ID"));
                 goodsListVO.setG_id(rs.getInt("G_ID"));
                 goodsListVO.setImg_path(rs.getString("IMG_PATH"));
                 goodsListVO.setG_name(rs.getString("G_NAME"));
                 goodsListVO.setG_data(rs.getString("G_DATA"));
                 goodsListVO.setG_datail(rs.getString("G_DATAIL"));
                 goodsListVO.setGd_price(rs.getDouble("GD_PRICE"));
                 goodsListVO.setGd_num(rs.getInt("GD_NUM"));
                 goodsListVO.setGfour_name(rs.getString("GFOUR_NAME"));
                 list.add(goodsListVO);
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return list;
     }

     @Override
     public List<GoodsListVO> queryAllGoodDetailLike(int s_id,String g_name) throws DAOException {
         List<GoodsListVO> list = new ArrayList<>();
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("select a.gd_id,b.g_id,c.img_path,b.g_name,a.gd_price,a.gd_num,d.gfour_name\n" +
                     "from good_detail a\n" +
                     "join goods b on a.g_id = b.g_id\n" +
                     "join img_path c on a.gd_id = c.gd_id\n" +
                     "join goods_four_type d on a.gfour_id = d.gfour_id\n" +
                     "where a.s_id=? and b.g_name regexp ?;");
             pstmt.setInt(1,s_id);
             pstmt.setString(2,g_name);
             rs = pstmt.executeQuery();
             while (rs.next()){
                 GoodsListVO goodsListVO = new GoodsListVO();
                 goodsListVO.setGd_id(rs.getInt("GD_ID"));
                 goodsListVO.setG_id(rs.getInt("G_ID"));
                 goodsListVO.setImg_path(rs.getString("IMG_PATH"));
                 goodsListVO.setG_name(rs.getString("G_NAME"));
                 goodsListVO.setGd_price(rs.getDouble("GD_PRICE"));
                 goodsListVO.setGd_num(rs.getInt("GD_NUM"));
                 goodsListVO.setGfour_name(rs.getString("GFOUR_NAME"));
                 list.add(goodsListVO);
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return list;
     }

     @Override
     public UpdateBindData getbackGoods(int gd_id) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("select a.uu_id,e.gft_id,e.gft_name,f.gst_id,f.gst_name,g.gtt_id,g.gtt_name,d.gfour_id,d.gfour_name,c.img_id,c.img_path,b.g_id,b.g_name,b.g_data,b.g_datail,a.gd_price,a.gd_num\n" +
                     "from good_detail a\n" +
                     "join goods b on a.g_id = b.g_id\n" +
                     "join img_path c on a.gd_id = c.gd_id\n" +
                     "join goods_four_type d on a.gfour_id = d.gfour_id\n" +
                     "join goods_first_type e on a.gft_id = e.gft_id\n" +
                     "join goods_second_type f on a.gst_id = f.gst_id\n" +
                     "join goods_third_type g on a.gtt_id = g.gtt_id\n" +
                     "where a.gd_id=?;");
             pstmt.setInt(1,gd_id);
             rs = pstmt.executeQuery();
             while (rs.next()){
                 UpdateBindData updateBindData = new UpdateBindData();
                 updateBindData.setUu_id(rs.getString("UU_ID"));
                 updateBindData.setGft_id(rs.getInt("GFT_ID"));
                 updateBindData.setGst_id(rs.getInt("GST_ID"));
                 updateBindData.setGtt_id(rs.getInt("GTT_ID"));
                 updateBindData.setGfour_id(rs.getInt("GFOUR_ID"));
                 updateBindData.setG_id(rs.getInt("G_ID"));
                 updateBindData.setImg_id(rs.getInt("IMG_ID"));
                 updateBindData.setGft_name(rs.getString("GFT_NAME"));
                 updateBindData.setGst_name(rs.getString("GST_NAME"));
                 updateBindData.setGtt_name(rs.getString("GTT_NAME"));
                 updateBindData.setGfour_name(rs.getString("GFOUR_NAME"));
                 updateBindData.setG_name(rs.getString("G_NAME"));
                 updateBindData.setG_data(rs.getString("G_DATA"));
                 updateBindData.setG_datail(rs.getString("G_DATAIL"));
                 updateBindData.setGd_price(rs.getDouble("GD_PRICE"));
                 updateBindData.setGd_num(rs.getInt("GD_NUM"));
                 updateBindData.setImg_path(rs.getString("IMG_PATH"));
                 return  updateBindData;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

     @Override
     public void deleteGoodsFourType(int gfour_id) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("DELETE FROM GOODS_FOUR_TYPE WHERE GFOUR_ID=?");
             pstmt.setInt(1,gfour_id);
             pstmt.executeUpdate();
         } catch (SQLException e) {
             e.printStackTrace();
             throw new DAOException(e);
         }finally{
             ConnectionUtils.close(rs, pstmt, null);
         }
     }

     @Override
     public List<Goods> queryAllGoods() throws DAOException {
         List<Goods> list = new ArrayList<>();
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOODS");
             rs = pstmt.executeQuery();
             while (rs.next()){
                 Goods goods = new Goods();
                 goods.setG_id(rs.getInt(1));
                 goods.setG_name(rs.getString(2));
                 goods.setG_data(rs.getString(3));
                 goods.setG_datail(rs.getString(4));
                 list.add(goods);
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return list;
     }

     @Override
     public GoodDetail queryGoodDetailByGfourId(int gfour_id) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOOD_DETAIL WHERE GFOUR_ID=?");
             pstmt.setInt(1,gfour_id);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 GoodDetail goodDetail = new GoodDetail();
                 int g_id = rs.getInt("G_ID");
                 goodDetail.setG_id(g_id);
                 int gd_id = rs.getInt("GD_ID");
                 goodDetail.setGd_id(gd_id);
                 int gft_id = rs.getInt("GFT_ID");
                 goodDetail.setGft_id(gft_id);
                 int gst_id = rs.getInt("GST_ID");
                 goodDetail.setGst_id(gst_id);
                 int gtt_id = rs.getInt("GTT_ID");
                 goodDetail.setGtt_id(gtt_id);
                 goodDetail.setGfour_id(gfour_id);
                 Double gd_price = rs.getDouble("GD_PRICE");
                 goodDetail.setGd_price(gd_price);
                 int gd_num = rs.getInt("GD_NUM");
                 goodDetail.setGd_num(gd_num);
                 int s_id = rs.getInt("S_ID");
                 goodDetail.setS_id(s_id);
                 int gd_buyNum = rs.getInt("GD_BUYNUM");
                 goodDetail.setGd_buyNum(gd_buyNum);
                 return goodDetail;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

     @Override
     public GoodDetail queryGoodDetailByUuid(String uu_id) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOOD_DETAIL WHERE UU_ID=?");
             pstmt.setString(1,uu_id);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 GoodDetail goodDetail = new GoodDetail();
                 int g_id = rs.getInt("G_ID");
                 goodDetail.setG_id(g_id);
                 int gd_id = rs.getInt("GD_ID");
                 goodDetail.setGd_id(gd_id);
                 int gft_id = rs.getInt("GFT_ID");
                 goodDetail.setGft_id(gft_id);
                 int gst_id = rs.getInt("GST_ID");
                 goodDetail.setGst_id(gst_id);
                 int gtt_id = rs.getInt("GTT_ID");
                 goodDetail.setGtt_id(gtt_id);
                 int gfour_id =  rs.getInt("GFOUR_ID");
                 goodDetail.setGfour_id(gfour_id);
                 Double gd_price = rs.getDouble("GD_PRICE");
                 goodDetail.setGd_price(gd_price);
                 int gd_num = rs.getInt("GD_NUM");
                 goodDetail.setGd_num(gd_num);
                 int s_id = rs.getInt("S_ID");
                 goodDetail.setS_id(s_id);
                 int gd_buyNum = rs.getInt("GD_BUYNUM");
                 goodDetail.setGd_buyNum(gd_buyNum);
                 goodDetail.setUu_id(uu_id);
                 return goodDetail;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

     @Override
     public List<GoodDetail> queryGoodDetailByGidSid(int g_id, int s_id) throws DAOException {
         List<GoodDetail> list = new ArrayList<>();
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("select * from good_detail where g_id=? and s_id=?;");
             pstmt.setInt(1,g_id);
             pstmt.setInt(2,s_id);
             rs = pstmt.executeQuery();
             while (rs.next()){
                 GoodDetail goodDetail = new GoodDetail();
                 goodDetail.setGd_id(rs.getInt("GD_ID"));
                 goodDetail.setGft_id(rs.getInt("GFT_ID"));
                 goodDetail.setGst_id(rs.getInt("GST_ID"));
                 goodDetail.setGtt_id(rs.getInt("GTT_ID"));
                 goodDetail.setGfour_id(rs.getInt("GFOUR_ID"));
                 goodDetail.setGd_price(rs.getDouble("GD_PRICE"));
                 goodDetail.setGd_num(rs.getInt("GD_NUM"));
                 goodDetail.setG_id(rs.getInt("G_ID"));
                 goodDetail.setS_id(rs.getInt("S_ID"));
                 goodDetail.setUu_id(rs.getString("UU_ID"));
                 list.add(goodDetail);
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return list;
     }

     @Override
     public void deleteImgPathByGdid(int gd_id) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("DELETE FROM IMG_PATH WHERE GD_ID=?");
             pstmt.setInt(1,gd_id);
             pstmt.executeUpdate();
         } catch (SQLException e) {
             e.printStackTrace();
             throw new DAOException(e);
         }finally{
             ConnectionUtils.close(rs, pstmt, null);
         }
     }

     @Override
     public List<Goods> queryAllGood(int s_id) throws DAOException {
         List<Goods> list = new ArrayList<>();
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("select distinct a.* from goods a\n" +
                     "join good_detail b on a.g_id = b.g_id \n" +
                     "where s_id =?;");
             pstmt.setInt(1,s_id);
             rs = pstmt.executeQuery();
             while (rs.next()){
                 Goods goods = new Goods();
                 goods.setG_id(rs.getInt("G_ID"));
                 goods.setG_name(rs.getString("G_NAME"));
                 goods.setG_data(rs.getString("G_DATA"));
                 goods.setG_datail(rs.getString("G_DATAIL"));
                 list.add(goods);
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return list;
     }

     @Override
    public Goods queryGoodsByName(String g_name) throws DAOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("SELECT * FROM GOODS WHERE G_NAME=?");
            pstmt.setString(1,g_name);
            rs = pstmt.executeQuery();
            if(rs.next()){
                Goods goods = new Goods();
                int g_id =rs.getInt("G_ID");
                goods.setG_id(g_id);
                goods.setG_name(g_name);
                String g_data = rs.getString("G_DATA");
                goods.setG_data(g_data);
                String g_datail = rs.getString("G_DATAIL");
                goods.setG_datail(g_datail);
                return goods;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw new DAOException(throwables);
        }finally {
            ConnectionUtils.close(rs,pstmt,null);
        }
        return null;
    }

     @Override
     public Goods queryGoodsById(int g_id) throws DAOException {
         Connection conn = null;
         PreparedStatement pstmt = null;
         ResultSet rs = null;
         try {
             conn = TransactionManager.getTransactionConn();
             pstmt = conn.prepareStatement("SELECT * FROM GOODS WHERE G_ID=?");
             pstmt.setInt(1,g_id);
             rs = pstmt.executeQuery();
             if(rs.next()){
                 Goods goods = new Goods();
                 goods.setG_id(g_id);
                 String g_name =rs.getString("G_NAME");
                 goods.setG_name(g_name);
                 String g_data = rs.getString("G_DATA");
                 goods.setG_data(g_data);
                 String g_datail = rs.getString("G_DATAIL");
                 goods.setG_datail(g_datail);
                 return goods;
             }
         } catch (SQLException throwables) {
             throwables.printStackTrace();
             throw new DAOException(throwables);
         }finally {
             ConnectionUtils.close(rs,pstmt,null);
         }
         return null;
     }

    @Override
    public void deleteImgPath(int img_id) throws DAOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("DELETE FROM IMG_PATH WHERE IMG_ID=?");
            pstmt.setInt(1,img_id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DAOException(e);
        }finally{
            ConnectionUtils.close(rs, pstmt, null);
        }
    }

    @Override
    public void deleteGoodDetail(int gd_id) throws DAOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("DELETE FROM GOOD_DETAIL WHERE GD_ID=?");
            pstmt.setInt(1,gd_id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DAOException(e);
        }finally{
            ConnectionUtils.close(rs, pstmt, null);
        }
    }

    @Override
    public void deleteGoods(int g_id) throws DAOException {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = TransactionManager.getTransactionConn();
            pstmt = conn.prepareStatement("DELETE FROM GOODS WHERE G_ID=?");
            pstmt.setInt(1,g_id);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DAOException(e);
        }finally{
            ConnectionUtils.close(rs, pstmt, null);
        }
    }

     public static void main(String[] args) throws DAOException {
         GoodsDaoImpl goodsDao = new GoodsDaoImpl();
         System.out.println(goodsDao.queryGoodDetailById(3));
     }
}
